package backend;

import java.sql.*;

public class DatabaseJDBCExample
{
    static java.util.Scanner in=new java.util.Scanner(System.in);
    
    public static void main(String[] args)
    {
        //Load driver
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e)
        {
            System.err.println("Error when loading DB driver:");
            System.err.println(e);
        }
        
        //Establish connection
        Connection conn = null;
        try
        {
            conn = DriverManager.getConnection("jdbc:mysql://bccs.ravens.bc/roomdraw",
                                   "vollun","spreadability");
            System.out.println("Connection to database established.");
        }
        catch (SQLException e)
        {
            System.err.println("SQL exception: " + e.getMessage());
            System.err.println("SQL state: " + e.getSQLState());
            System.err.println("Error code: " + e.getErrorCode());
        }
        
        String fname, lname;
        int rnum;
        PreparedStatement p = null;
        
        try
        {
            p = conn.prepareStatement(
                      "SELECT * "
                    + "FROM USER ");
        }
        catch (SQLException e)
        {
            System.err.println("SQL exception: " + e.getMessage());
            System.err.println("SQL state: " + e.getSQLState());
            System.err.println("Error code: " + e.getErrorCode());
        }
        
        ResultSet r = null;
        try
        {
            r = p.executeQuery();
            while (r.next())
            {
                fname = r.getString("First_Name");
                lname = r.getString("Last_Name");
                rnum = r.getInt("Rnd_Num");
                
                System.out.println(fname+" "+lname+"\'s random number is "+rnum);
            }
        }
        catch (SQLException e)
        {
            System.err.println("SQL exception: " + e.getMessage());
            System.err.println("SQL state: " + e.getSQLState());
            System.err.println("Error code: " + e.getErrorCode());
        }
        
        if (r != null)
        {
            try
            {
                r.close();
            }
            catch (SQLException ex)
            {
            }
        }
        if (p != null)
        {
            try
            {
                p.close();
            }
            catch (SQLException ex)
            {
            }
        }
        //Close connection
        if (conn != null)
        {
            try
            {
                conn.close();
                System.out.println("Database connection closed.");
            }
            catch (SQLException ex)
            {
            }
        }
    }
}
